﻿using nsBaseClass;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;

using System.Collections;
using log4net;
using AMebooking.Models;
using AMebooking.NetUtils;

namespace AMebooking.Database
{
    public class DBAccess
    {
        protected static readonly ILog logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        public static List<Department> getDepartment()
        {

            var departments = new List<Department>();
            var globalVars = new clsGlobalVariable();
            var hSql = new clsSqlFactory();
            var objUtil = new clsBaseUtility();
            try
            {
                //C1: department ID 
                //C2: department name
                string sql = "select C1 as id , C2 as name From " + objUtil.getTable("CORW") + " Where CODAID='OSASTOT' and C3 like '%K%' order by C2;";
                hSql.NewCommand(sql);
                hSql.ExecuteReader();

                while (hSql.Read())
                {
                    var reader = hSql.Reader;

                    var code = reader.IsDBNull(reader.GetOrdinal("id")) ? "" : reader.GetString(reader.GetOrdinal("id"));
                    var description = reader.IsDBNull(reader.GetOrdinal("name")) ? "" : reader.GetString(reader.GetOrdinal("name"));

                    // var code = reader.GetString(reader.GetOrdinal("id"));
                    //var description = reader.GetString(reader.GetOrdinal("name"));
                    var de = new Department(code, description);
                    departments.Add(de);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
            }
            finally
            {
                hSql.Close();
            }
            return departments;

        }

        public static List<Mechanic> getMechanic(string smanidReq, string deptid, string siteid)
        {
            var mechanics = new List<Mechanic>();
            var globalVars = new clsGlobalVariable();
            var hSql = new clsSqlFactory();
            var objUtil = new clsBaseUtility();

            try
            {
                //string sql = "select SMANID, NAME, DEPT,SUBSYS, from SMAN where SUBSYS like '%M%' order by NAME";
                string sql = "select SMANID,PHONE,NAME,Dept,EMAIL,SUBSYS,UNITID from " + objUtil.getTable("SMAN") + " s where (SUBSYS like '%M%' or SUBSYS like '%K%') ";
                if (string.IsNullOrEmpty(siteid))
                {
                    sql += " and s.UNITID in ('" + globalVars.CurrentSiteId + "') ";
                }
                else
                {
                    sql += " and s.UNITID in ('" + siteid + "') ";
                }
                string endSql = " order by NAME";
                if (!string.IsNullOrEmpty(smanidReq) && !"0".Equals(smanidReq))
                    sql += " and s.SMANID like \'" + smanidReq + "\'";
                if (!string.IsNullOrEmpty(deptid) && !"0".Equals(deptid))
                    sql += " and s.Dept like \'" + deptid + "\'";

                sql += endSql;


                hSql.NewCommand(sql);
                hSql.ExecuteReader();

                while (hSql.Read())
                {
                    var reader = hSql.Reader;

                    var smanid = reader.IsDBNull(reader.GetOrdinal("SMANID")) ? "" : reader.GetString(reader.GetOrdinal("SMANID"));
                    var name = reader.IsDBNull(reader.GetOrdinal("NAME")) ? "" : reader.GetString(reader.GetOrdinal("NAME"));
                    var dept = reader.IsDBNull(reader.GetOrdinal("Dept")) ? "" : reader.GetString(reader.GetOrdinal("Dept"));
                    var phone = reader.IsDBNull(reader.GetOrdinal("PHONE")) ? "" : reader.GetString(reader.GetOrdinal("PHONE"));
                    var email = reader.IsDBNull(reader.GetOrdinal("EMAIL")) ? "" : reader.GetString(reader.GetOrdinal("EMAIL"));
                    var subsystem = reader.IsDBNull(reader.GetOrdinal("SUBSYS")) ? "" : reader.GetString(reader.GetOrdinal("SUBSYS"));
                    var unitid = reader.IsDBNull(reader.GetOrdinal("UNITID")) ? "" : reader.GetString(reader.GetOrdinal("UNITID"));



                    Mechanic m = new Mechanic(smanid, name, dept, phone, email, subsystem, unitid);
                    mechanics.Add(m);
                    // logger.Debug(m.ToString());

                }
                //material resources
                 sql = "select RESID,NAME,_UNITID from ALL_GRES s where 1=1 ";
                if (string.IsNullOrEmpty(siteid))
                {
                    sql += " and s._UNITID in ('" + globalVars.CurrentSiteId + "') ";
                }
                else
                {
                    sql += " and s._UNITID in ('" + siteid + "') ";
                }
                endSql = " order by NAME";
                if (!string.IsNullOrEmpty(smanidReq) && !"0".Equals(smanidReq))
                    sql += " and s.RESID like \'" + smanidReq + "\'";
              

                sql += endSql;
                hSql.NewCommand(sql);
                hSql.ExecuteReader();

                while (hSql.Read())
                {
                    var reader = hSql.Reader;

                    var smanid = reader.IsDBNull(reader.GetOrdinal("RESID")) ? "" : reader.GetString(reader.GetOrdinal("RESID"));
                    var name = reader.IsDBNull(reader.GetOrdinal("NAME")) ? "" : reader.GetString(reader.GetOrdinal("NAME"));
                    var unitid = reader.IsDBNull(reader.GetOrdinal("_UNITID")) ? "" : reader.GetString(reader.GetOrdinal("_UNITID"));



                    Mechanic m = new Mechanic(smanid, name, "", "", "", "M", unitid);
                    mechanics.Add(m);
                    // logger.Debug(m.ToString());

                }

            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
            }
            finally
            {
                hSql.Close();
            }
            return mechanics;
        }

        public static List<Mechanic> getMechanicV2(string smanidReq, string deptid, string siteid, string licno)
        {
            var mechanics = new List<Mechanic>();
            var globalVars = new clsGlobalVariable();
            var hSql = new clsSqlFactory();
            var objUtil = new clsBaseUtility();

            try
            {


                string sql = "select *  from v_ReservationTime t where 1=1 ";
                if (string.IsNullOrEmpty(siteid))
                {
                    sql += " and t.UNITID in ('" + globalVars.CurrentSiteId + "') ";
                }
                else
                {
                    sql += " and t.UNITID in ('" + siteid + "') ";
                }
                if (!string.IsNullOrEmpty(deptid) && !"0".Equals(deptid))
                    sql += " and t.DEPTID like '" + deptid + "'";
                if (!string.IsNullOrEmpty(smanidReq) && !"0".Equals(smanidReq))
                    sql += " and t.SMANID like '" + smanidReq + "'";

                if (!string.IsNullOrEmpty(licno))
                    sql += " and t.LIC_NO like '%" + licno + "%'";

                sql += " order by t.SNAME";

                hSql.NewCommand(sql);
                hSql.ExecuteReader();

                while (hSql.Read())
                {
                    var reader = hSql.Reader;

                    var smanid = reader.IsDBNull(reader.GetOrdinal("SMANID")) ? "" : reader.GetString(reader.GetOrdinal("SMANID"));
                    var name = reader.IsDBNull(reader.GetOrdinal("SNAME")) ? "" : reader.GetString(reader.GetOrdinal("SNAME"));
                    var dept = reader.IsDBNull(reader.GetOrdinal("DEPTID")) ? "" : reader.GetString(reader.GetOrdinal("DEPTID"));
                    var unitid = reader.IsDBNull(reader.GetOrdinal("UNITID")) ? "" : reader.GetString(reader.GetOrdinal("UNITID"));

                    Mechanic m = new Mechanic(smanid, name, dept, string.Empty, string.Empty, string.Empty, unitid);
                    if (!mechanics.Contains(m))
                    {
                        mechanics.Add(m);
                    }

                }

            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
            }
            finally
            {
                hSql.Close();
            }
            return mechanics;
        }


        public static List<Reservation> getCalendar(string DeptId, string smanid, DateTime? fromdate, DateTime? todate, int? workorderno, string siteid)
        {
            var reservations = new List<Reservation>();
            var globalVars = new clsGlobalVariable();
            var hSql = new clsSqlFactory();
            var objUtil = new clsBaseUtility();

            try
            {
                string sql = "select * from v_ReservationTime t where 1=1 ";
                if (string.IsNullOrEmpty(siteid))
                {
                    sql += " and t.UNITID in ('" + globalVars.CurrentSiteId + "') ";
                }
                else
                {
                    sql += " and t.UNITID in ('" + siteid + "') ";
                }

                if (!string.IsNullOrEmpty(DeptId))
                    sql += " and t.DEPTID like '" + DeptId + "'";
                if (!string.IsNullOrEmpty(smanid))
                    sql += " and t.SMANID like '" + smanid + "'";
                if (fromdate != null)
                {
                    DateTime d = fromdate ?? default(DateTime);
                    sql += " and datediff(MINUTE,t.STARTTIME,'" + d.ToString("yyyy-MM-dd HH:mm:ss") + "')<=0";
                }

                if (todate != null)
                {
                    DateTime d = todate ?? default(DateTime);
                    sql += " and datediff(MINUTE,t.ENDTIME,'" + d.ToString("yyyy-MM-dd HH:mm:ss") + "')>=0";
                }

                if (workorderno != null & workorderno != -1)
                    sql += "and t.WORK_ORDER_ID = " + workorderno;

                hSql.NewCommand(sql);
                hSql.ExecuteReader();

                while (hSql.Read())
                {
                    var reader = hSql.Reader;
                    DateTime? startTime = null;
                    DateTime? endTime = null;
                    startTime = reader.IsDBNull(reader.GetOrdinal("STARTTIME")) ? startTime : reader.GetDateTime(reader.GetOrdinal("STARTTIME"));
                    endTime = reader.IsDBNull(reader.GetOrdinal("ENDTIME")) ? endTime : reader.GetDateTime(reader.GetOrdinal("ENDTIME"));

                    decimal? duration = null;
                    duration = reader.IsDBNull(reader.GetOrdinal("DURATION")) ? duration : (reader.GetDecimal(reader.GetOrdinal("DURATION")));

                    //var deptiD = reader.IsDBNull(reader.GetOrdinal("DeptId")) ? "" : (reader.GetString(reader.GetOrdinal("DeptId")));
                    //var dept = reader.IsDBNull(reader.GetOrdinal("Dept")) ? "" : (reader.GetString(reader.GetOrdinal("Dept")));

                    DateTime? pickupDate = null;
                    pickupDate = reader.IsDBNull(reader.GetOrdinal("PICKUP_DATE")) ? pickupDate : (reader.GetDateTime(reader.GetOrdinal("PICKUP_DATE")));

                    DateTime? pickupTime = null;
                    pickupTime = reader.IsDBNull(reader.GetOrdinal("PICKUP_TIME")) ? pickupTime : (reader.GetDateTime(reader.GetOrdinal("PICKUP_TIME")));

                    var smanId = reader.IsDBNull(reader.GetOrdinal("SMANID")) ? "" : reader.GetString(reader.GetOrdinal("SMANID"));

                    var sname = reader.IsDBNull(reader.GetOrdinal("SNAME")) ? "" : reader.GetString(reader.GetOrdinal("SNAME"));

                    int? workorderNo = null;
                    workorderNo = reader.IsDBNull(reader.GetOrdinal("WORK_ORDER_ID")) ? workorderNo : reader.GetInt32(reader.GetOrdinal("WORK_ORDER_ID"));

                    var transactionType = reader.IsDBNull(reader.GetOrdinal("TRANSACTION_TYPE")) ? "" : reader.GetString(reader.GetOrdinal("TRANSACTION_TYPE"));

                    var transactionNote = reader.IsDBNull(reader.GetOrdinal("TRANSACTION_NOTE")) ? "" : reader.GetString(reader.GetOrdinal("TRANSACTION_NOTE"));

                    var licenseNo = reader.IsDBNull(reader.GetOrdinal("LIC_NO")) ? "" : reader.GetString(reader.GetOrdinal("LIC_NO"));

                    var make = reader.IsDBNull(reader.GetOrdinal("MAKE")) ? "" : reader.GetString(reader.GetOrdinal("MAKE"));
                    var model = reader.IsDBNull(reader.GetOrdinal("MODEL")) ? "" : reader.GetString(reader.GetOrdinal("MODEL"));

                    var subModel = reader.IsDBNull(reader.GetOrdinal("SUBMODEL")) ? "" : reader.GetString(reader.GetOrdinal("SUBMODEL"));

                    int? customerNo = null;
                    customerNo = reader.IsDBNull(reader.GetOrdinal("CUSTNO")) ? customerNo : reader.GetInt32(reader.GetOrdinal("CUSTNO"));

                    var fName = reader.IsDBNull(reader.GetOrdinal("FNAME")) ? "" : reader.GetString(reader.GetOrdinal("FNAME"));
                    var lName = reader.IsDBNull(reader.GetOrdinal("LNAME")) ? "" : reader.GetString(reader.GetOrdinal("LNAME"));


                    var conFName = reader.IsDBNull(reader.GetOrdinal("CONFNAME")) ? "" : reader.GetString(reader.GetOrdinal("CONFNAME"));

                    var conLName = reader.IsDBNull(reader.GetOrdinal("CONLNAME")) ? "" : reader.GetString(reader.GetOrdinal("CONLNAME"));

                    var contactTel = reader.IsDBNull(reader.GetOrdinal("CONTACT_TEL")) ? "" : reader.GetString(reader.GetOrdinal("CONTACT_TEL"));

                    var contactEmail = reader.IsDBNull(reader.GetOrdinal("CONTACT_EMAIL")) ? "" : reader.GetString(reader.GetOrdinal("CONTACT_EMAIL"));

                    var workDescription = reader.IsDBNull(reader.GetOrdinal("NOTE")) ? "" : reader.GetString(reader.GetOrdinal("NOTE"));

                    var deptid = reader.IsDBNull(reader.GetOrdinal("DEPTID")) ? "" : reader.GetString(reader.GetOrdinal("DEPTID"));
                    //var dept = reader.IsDBNull(reader.GetOrdinal("DEPT")) ? "" : reader.GetString(reader.GetOrdinal("DEPT"));

                    var durationStr = "";

                    if (duration != null)
                    {
                        var hours = ((int)duration).ToString();
                        var min = ((int)((duration - (int)duration) * 60)).ToString();
                        durationStr = hours + " hours " + min + " mins";
                    }


                    Reservation r = new Reservation(startTime, endTime, durationStr, pickupDate, pickupTime, smanId, sname, workorderNo,
                        transactionType, transactionNote, licenseNo, make, model, subModel, customerNo, fName, lName, conFName, conLName,
                        contactTel, contactEmail, workDescription, deptid);
                    r.Type = "P"; // fixed
                    reservations.Add(r);

                    //logger.Debug(duration.ToString());

                }

            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
            }
            finally
            {
                hSql.Close();
            }
            return reservations;
        }






        public static List<Calendar> getCalendar2(DateTime starttime, DateTime endtime, string licno, string reservationNo)
        {
            List<Calendar> ret = new List<Calendar>();
            var globalVars = new clsGlobalVariable();
            var hSql = new clsSqlFactory();
            var objUtil = new clsBaseUtility();

            string sql = "select g.RTYPE, g.NOTE, s.SMANID, gt.STARTDT as STARTTIME,gt.ENDDT as ENDTIME,gs.STYPE as TYPE, v.LICNO, gs.WRKORDNO,"

                        + " from  "
                        + objUtil.getTable("GROW") + " g, "
                        + objUtil.getTable("GSAL") + "  gs, "
                        + objUtil.getTable("GTRES") + "  gt, "
                        + objUtil.getTable("SMAN") + " s , "
                        + objUtil.getTable("VEHI") + "  v"
                        + " where g.GSALID = gs.GSALID and s.SMANID = gt.RESID and gs.RECEIVER=s.SMANID and g.RTYPE =7 ";
            if (!string.IsNullOrEmpty(licno))
                sql += " and v.LICNO like \'" + licno + "\' ";
            if (!string.IsNullOrEmpty(reservationNo))
                sql += " and gs.WRKORDNO like \'" + reservationNo + "\' ";

            if (starttime != null)
                sql += " and datediff(MINUTE,gt.STARTDT,'" + starttime.ToString("yyyy-MM-dd HH:mm:ss") + "')<=0";
            if (endtime != null)
                sql += " and datediff(MINUTE,gt.ENDDT,'" + endtime.ToString("yyyy-MM-dd HH:mm:ss") + "')>=0";



            hSql.NewCommand(sql);
            hSql.ExecuteReader();


            while (hSql.Read())
            {
                var reader = hSql.Reader;
                DateTime? startTime = null;
                DateTime? endTime = null;
                startTime = reader.IsDBNull(reader.GetOrdinal("STARTTIME")) ? startTime : reader.GetDateTime(reader.GetOrdinal("STARTTIME"));
                endTime = reader.IsDBNull(reader.GetOrdinal("ENDTIME")) ? endTime : reader.GetDateTime(reader.GetOrdinal("ENDTIME"));
                var smanid = reader.IsDBNull(reader.GetOrdinal("SMANID")) ? "" : reader.GetString(reader.GetOrdinal("SMANID"));
                var type = reader.IsDBNull(reader.GetOrdinal("TYPE")) ? "" : reader.GetString(reader.GetOrdinal("TYPE"));
                var note = reader.IsDBNull(reader.GetOrdinal("NOTE")) ? "" : reader.GetString(reader.GetOrdinal("NOTE"));
                Calendar c = new Calendar();
                c.starttime = startTime ?? default(DateTime);
                c.endtime = endTime ?? default(DateTime);
                c.type = type;
                c.note = note;
                c.smanid = smanid;
                ret.Add(c);
            }

            return ret;
        }



        //public static void getResourcesReservationObj(DateTime starttime, DateTime endtime, string smanid, string deptid)
        //{
        //    string sql = "SELECT s.DEPT AS DEPTID, s.SMANID, s.NAME AS SNAME, "
        //    + "gs.GSALID, gs.WRKORDNO AS WORK_ORDER_ID,gs.VEHIID,"
        //    + "gs.CUSTNO,gs.WTRID, gs.CONFNAME,gs.CONLNAME,gs.UWTEL as CONTACT_TEL, gs.UEMAIL as CONTACT_EMAIL, "
        //    + "gt.STARTDT as STARTTIME, gt.ENDDT as ENDTIME, "
        //    + "gt.DURATION, v. VEHIID, v.LICNO AS LIC_NO, "
        //    + "v.SERIALNO, v.MAKE, v.MODEL,v.SUBMODEL,"
        //    + "c.CUSTNO, c.LNAME, c.FNAME, c.EMAIL, c.WTEL,"
        //    + "gs.STYPE as TRANSACTION_TYPE,c2.C2 as TRANSACTION_NOTE ,gs.ESTDELD as PICKUP_DATE ,gs.ESTDELTIME AS PICKUP_TIME, "
        //    + "gr.NOTE , gs.RECEIVER "

        //    + "FROM SMAN s, GSAL gs, VEHI v, CUST c, GTRS g, GTRES gt,GROW gr, CORW c2"

        //    + " WHERE gs.VEHIID = v.VEHIID and gs.CUSTNO = c.CUSTNO "
        //    + " and gt.WTRID = g.WTRID and  gs.WTRID = g.WTRID and gt.RESID = s.SMANID"
        //    + " and gr.GSALID = gs.GSALID and gs.RECEIVER = s.SMANID "
        //    + "	and c2.C1 = gs.STYPE and CODAID='LIIKETAPTY' and c2.C7 like '%K%' and gr.RTYPE=7";

        //    var globalVars = new clsGlobalVariable();
        //    var hSql = new clsSqlFactory();
        //    var objUtil = new clsBaseUtility();

        //    hSql.NewCommand(sql);
        //    hSql.ExecuteReader();
        //    while (hSql.Read())
        //    {
        //        var reader = hSql.Reader;
        //        var confname = reader.IsDBNull(reader.GetOrdinal("CONFNAME")) ? "" : reader.GetString(reader.GetOrdinal("CONFNAME"));
        //        var conlname = reader.IsDBNull(reader.GetOrdinal("CONLNAME")) ? "" : reader.GetString(reader.GetOrdinal("CONLNAME"));
        //    }
        //}



        public static List<TimeReservation> getWorkOrders(string DeptId, string smanid, DateTime? fromdate,
            DateTime? todate, int? workorderno, string licno, string siteid)
        {
            var globalVars = new clsGlobalVariable();
            var hSql = new clsSqlFactory();
            var objUtil = new clsBaseUtility();
            List<TimeReservation> ret = new List<TimeReservation>();

            List<Contact> retContact = new List<Contact>();
            List<Vehicle> retVehicle = new List<Vehicle>();

            string sql = "select *  from v_ReservationTime t where 1=1 ";
            if (string.IsNullOrEmpty(siteid))
            {
                sql += " and t.UNITID in ('" + globalVars.CurrentSiteId + "') ";
            }
            else
            {
                sql += " and t.UNITID in ('" + siteid + "') ";
            }
            if (!string.IsNullOrEmpty(DeptId) && !"0".Equals(DeptId))
                sql += " and t.DEPTID like '" + DeptId + "'";
            if (!string.IsNullOrEmpty(smanid) && !"0".Equals(smanid))
                sql += " and t.SMANID like '" + smanid + "'";
            if (fromdate != null)
            {
                DateTime d = fromdate ?? default(DateTime);
                sql += " and datediff(MINUTE,t.STARTTIME,'" + d.ToString("yyyy-MM-dd HH:mm:ss") + "')<=0";
            }
            if (todate != null)
            {
                DateTime d = todate ?? default(DateTime);
                sql += " and datediff(MINUTE,t.ENDTIME,'" + d.ToString("yyyy-MM-dd HH:mm:ss") + "')>=0";
            }

            if (workorderno != null & workorderno != -1)
                sql += "and t.WORK_ORDER_ID = " + workorderno;

            if (!string.IsNullOrEmpty(licno))
                sql += "and t.LIC_NO like '%" + licno + "%'";

            hSql.NewCommand(sql);
            hSql.ExecuteReader();
            while (hSql.Read())
            {
                var reader = hSql.Reader;
                var confname = reader.IsDBNull(reader.GetOrdinal("CONFNAME")) ? "" : reader.GetString(reader.GetOrdinal("CONFNAME"));
                var conlname = reader.IsDBNull(reader.GetOrdinal("CONLNAME")) ? "" : reader.GetString(reader.GetOrdinal("CONLNAME"));
                var tel = reader.IsDBNull(reader.GetOrdinal("CONTACT_TEL")) ? "" : reader.GetString(reader.GetOrdinal("CONTACT_TEL"));
                var email = reader.IsDBNull(reader.GetOrdinal("CONTACT_EMAIL")) ? "" : reader.GetString(reader.GetOrdinal("CONTACT_EMAIL"));

                Contact c = new Contact();
                c.ContactName = confname + " " + conlname;
                c.ContactEmail = email;
                c.ContactTel = tel;
                retContact.Add(c);

                var licenseNo = reader.IsDBNull(reader.GetOrdinal("LIC_NO")) ? "" : reader.GetString(reader.GetOrdinal("LIC_NO"));
                var make = reader.IsDBNull(reader.GetOrdinal("MAKE")) ? "" : reader.GetString(reader.GetOrdinal("MAKE"));
                var model = reader.IsDBNull(reader.GetOrdinal("MODEL")) ? "" : reader.GetString(reader.GetOrdinal("MODEL"));
                var subModel = reader.IsDBNull(reader.GetOrdinal("SUBMODEL")) ? "" : reader.GetString(reader.GetOrdinal("SUBMODEL"));

                Vehicle v = new Vehicle();
                v.LicenseNumber = licenseNo;
                v.Model = make + " " + model + " " + subModel;
                retVehicle.Add(v);

                DateTime? pickupDate = null;
                pickupDate = reader.IsDBNull(reader.GetOrdinal("PICKUP_DATE")) ? pickupDate : (reader.GetDateTime(reader.GetOrdinal("PICKUP_DATE")));

                DateTime? pickupTime = null;
                pickupTime = reader.IsDBNull(reader.GetOrdinal("PICKUP_TIME")) ? pickupTime : (reader.GetDateTime(reader.GetOrdinal("PICKUP_TIME")));

                var smanId = reader.IsDBNull(reader.GetOrdinal("SMANID")) ? "" : reader.GetString(reader.GetOrdinal("SMANID"));

                var sname = reader.IsDBNull(reader.GetOrdinal("SNAME")) ? "" : reader.GetString(reader.GetOrdinal("SNAME"));

                int? workorderNo = null;
                workorderNo = reader.IsDBNull(reader.GetOrdinal("WORK_ORDER_ID")) ? workorderNo : reader.GetInt32(reader.GetOrdinal("WORK_ORDER_ID"));
                decimal? duration = null;
                duration = reader.IsDBNull(reader.GetOrdinal("DURATION")) ? duration : (reader.GetDecimal(reader.GetOrdinal("DURATION")));
                var durationStr = "";

                if (duration != null)
                {
                    var hours = ((int)duration).ToString();
                    var min = ((int)((duration - (int)duration) * 60)).ToString();
                    durationStr = hours + " hours " + min + " mins";
                }
                DateTime? startTime = null;
                DateTime? endTime = null;
                startTime = reader.IsDBNull(reader.GetOrdinal("STARTTIME")) ? startTime : reader.GetDateTime(reader.GetOrdinal("STARTTIME"));
                endTime = reader.IsDBNull(reader.GetOrdinal("ENDTIME")) ? endTime : reader.GetDateTime(reader.GetOrdinal("ENDTIME"));


                TimeReservation tr = new TimeReservation();
                tr.Contact = c;
                tr.Vehicle = v;
                tr.StartTime = startTime ?? default(DateTime);
                tr.EndTime = endTime ?? default(DateTime);
                tr.Duration = durationStr;
                //tr.SmanId = smanid;
                tr.ReservationNumber = workorderNo ?? default(int);
                ret.Add(tr);

            }

            return ret;
        }

        public static List<TimeReservation> getResourcesReservationObj(List<TimeReservation> input, string siteid)
        {
            List<TimeReservation> ret = new List<TimeReservation>();
            var globalVars = new clsGlobalVariable();
            var hSql = new clsSqlFactory();
            var objUtil = new clsBaseUtility();


            foreach (TimeReservation t in input)
            {
                
                List<Resource> retResource = new List<Resource>();
                //string sql = "select t.DURATION,t.ENDTIME as ENDDT,t.STARTTIME as STARTDT, t.SMANID as RESID from dbo.v_ReservationTime t where t.WORK_ORDER_ID = {0}";
                string sql = "select t.DURATION,t.ENDTIME as ENDDT,t.STARTTIME as STARTDT, t.SMANID as RESID, * from v_ReservationTime  t where t.WORK_ORDER_ID = {0}";
                if (string.IsNullOrEmpty(siteid))
                {
                    sql += " and t.UNITID in ('" + globalVars.CurrentSiteId + "') ";
                }
                else
                {
                    sql += " and t.UNITID in ('" + siteid + "') ";
                }
                // Limit resource in day range
                if (t.StartTime != null)
                    sql += " and datediff(MINUTE,t.STARTTIME,'" + t.StartTime.ToString("yyyy-MM-dd HH:mm:ss") + "')<=0";
                if (t.EndTime != null)
                    sql += " and datediff(MINUTE,t.ENDTIME,'" + t.EndTime.ToString("yyyy-MM-dd HH:mm:ss") + "')>=0";

                //string sql = "select * from dbo.GTRES gt where gt.WTRID = {0}";
                sql = string.Format(sql, t.ReservationNumber);
                hSql.NewCommand(sql);
                hSql.ExecuteReader();
                while (hSql.Read())
                {
                    var reader = hSql.Reader;
                    decimal? duration = null;
                    duration = reader.IsDBNull(reader.GetOrdinal("DURATION")) ? duration : (reader.GetDecimal(reader.GetOrdinal("DURATION")));
                    var durationStr = "";

                    if (duration != null)
                    {
                        var hours = ((int)duration).ToString();
                        var min = ((int)((duration - (int)duration) * 60)).ToString();
                        durationStr = hours + " hours " + min + " mins";
                    }
                    DateTime? startTime = null;
                    DateTime? endTime = null;
                    startTime = reader.IsDBNull(reader.GetOrdinal("STARTDT")) ? startTime : reader.GetDateTime(reader.GetOrdinal("STARTDT"));
                    endTime = reader.IsDBNull(reader.GetOrdinal("ENDDT")) ? endTime : reader.GetDateTime(reader.GetOrdinal("ENDDT"));
                    var smanId = reader.IsDBNull(reader.GetOrdinal("RESID")) ? "" : reader.GetString(reader.GetOrdinal("RESID"));

                    // add extra info

                    //var deptiD = reader.IsDBNull(reader.GetOrdinal("DeptId")) ? "" : (reader.GetString(reader.GetOrdinal("DeptId")));
                    //var dept = reader.IsDBNull(reader.GetOrdinal("Dept")) ? "" : (reader.GetString(reader.GetOrdinal("Dept")));

                    DateTime? pickupDate = null;
                    pickupDate = reader.IsDBNull(reader.GetOrdinal("PICKUP_DATE")) ? pickupDate : (reader.GetDateTime(reader.GetOrdinal("PICKUP_DATE")));

                    DateTime? pickupTime = null;
                    pickupTime = reader.IsDBNull(reader.GetOrdinal("PICKUP_TIME")) ? pickupTime : (reader.GetDateTime(reader.GetOrdinal("PICKUP_TIME")));

                    var sname = reader.IsDBNull(reader.GetOrdinal("SNAME")) ? "" : reader.GetString(reader.GetOrdinal("SNAME"));

                    int? workorderNo = null;
                    workorderNo = reader.IsDBNull(reader.GetOrdinal("WORK_ORDER_ID")) ? workorderNo : reader.GetInt32(reader.GetOrdinal("WORK_ORDER_ID"));

                    var transactionType = reader.IsDBNull(reader.GetOrdinal("TRANSACTION_TYPE")) ? "" : reader.GetString(reader.GetOrdinal("TRANSACTION_TYPE"));

                    var transactionNote = reader.IsDBNull(reader.GetOrdinal("TRANSACTION_NOTE")) ? "" : reader.GetString(reader.GetOrdinal("TRANSACTION_NOTE"));

                    var licenseNo = reader.IsDBNull(reader.GetOrdinal("LIC_NO")) ? "" : reader.GetString(reader.GetOrdinal("LIC_NO"));

                    var make = reader.IsDBNull(reader.GetOrdinal("MAKE")) ? "" : reader.GetString(reader.GetOrdinal("MAKE"));
                    var model = reader.IsDBNull(reader.GetOrdinal("MODEL")) ? "" : reader.GetString(reader.GetOrdinal("MODEL"));

                    var subModel = reader.IsDBNull(reader.GetOrdinal("SUBMODEL")) ? "" : reader.GetString(reader.GetOrdinal("SUBMODEL"));

                    int? customerNo = null;
                    customerNo = reader.IsDBNull(reader.GetOrdinal("CUSTNO")) ? customerNo : reader.GetInt32(reader.GetOrdinal("CUSTNO"));

                    var fName = reader.IsDBNull(reader.GetOrdinal("FNAME")) ? "" : reader.GetString(reader.GetOrdinal("FNAME"));
                    var lName = reader.IsDBNull(reader.GetOrdinal("LNAME")) ? "" : reader.GetString(reader.GetOrdinal("LNAME"));


                    var conFName = reader.IsDBNull(reader.GetOrdinal("CONFNAME")) ? "" : reader.GetString(reader.GetOrdinal("CONFNAME"));

                    var conLName = reader.IsDBNull(reader.GetOrdinal("CONLNAME")) ? "" : reader.GetString(reader.GetOrdinal("CONLNAME"));

                    var contactTel = reader.IsDBNull(reader.GetOrdinal("CONTACT_TEL")) ? "" : reader.GetString(reader.GetOrdinal("CONTACT_TEL"));

                    var contactEmail = reader.IsDBNull(reader.GetOrdinal("CONTACT_EMAIL")) ? "" : reader.GetString(reader.GetOrdinal("CONTACT_EMAIL"));

                    var workDescription = reader.IsDBNull(reader.GetOrdinal("NOTE")) ? "" : reader.GetString(reader.GetOrdinal("NOTE"));

                    var deptid = reader.IsDBNull(reader.GetOrdinal("DEPTID")) ? "" : reader.GetString(reader.GetOrdinal("DEPTID"));
                    //var dept = reader.IsDBNull(reader.GetOrdinal("DEPT")) ? "" : reader.GetString(reader.GetOrdinal("DEPT"));

                    var stamid = reader.IsDBNull(reader.GetOrdinal("STAMID")) ? -1 : reader.GetInt32(reader.GetOrdinal("STAMID"));
                    DateTime? servDate = null;
                        servDate = reader.IsDBNull(reader.GetOrdinal("SERVD")) ? servDate : (reader.GetDateTime(reader.GetOrdinal("SERVD")));
                    DateTime? wendDate = null;
                        wendDate = reader.IsDBNull(reader.GetOrdinal("WENDD")) ? wendDate : (reader.GetDateTime(reader.GetOrdinal("WENDD")));
                    DateTime? endDate = null;
                        endDate = reader.IsDBNull(reader.GetOrdinal("WENDDT")) ? endDate : (reader.GetDateTime(reader.GetOrdinal("WENDDT"))); 

                    Reservation re = new Reservation(startTime, endTime, durationStr, pickupDate, pickupTime, smanId, sname, workorderNo,
                        transactionType, transactionNote, licenseNo, make, model, subModel, customerNo, fName, lName, conFName, conLName,
                        contactTel, contactEmail, workDescription, deptid);
                    re.STAMID = stamid;
                    re.SERVDate = servDate;
                    re.WENDDate = wendDate;
                    re.ENDDate = endDate;
                    re.Type = "P"; // fixed
                    re.StampStatus = reader.IsDBNull(reader.GetOrdinal("STAMP_STATUS")) ? "" : reader.GetString(reader.GetOrdinal("STAMP_STATUS"));
                    re.WOREADYDATE = reader.IsDBNull(reader.GetOrdinal("WOREADYDATE")) ? DateTime.MinValue : (reader.GetDateTime(reader.GetOrdinal("WOREADYDATE")));
                    re.CUSTWAIT = reader.IsDBNull(reader.GetOrdinal("CUSTWAIT")) ? 0 : reader.GetInt16(reader.GetOrdinal("CUSTWAIT"));
                    re.WOSTATUS = reader.IsDBNull(reader.GetOrdinal("WOSTATUS")) ? "A" : reader.GetString(reader.GetOrdinal("WOSTATUS"));
                  
                    Resource r = new Resource();
                    r.Duration = durationStr;
                    r.EndTime = endTime ?? default(DateTime);
                    r.StartTime = startTime ?? default(DateTime);
                    r.ResourceId = smanId;
                    r.ReservationInfo = re;
                    
                    retResource.Add(r);

                }
                t.Resources = retResource;
                ret.Add(t);

            }




            return ret;

        }




    }
}